﻿Imports DTOLayer
Imports System.Data.SqlClient
Public Class QuyDinhDao
    Public Sub Them(ByVal QDDto As QuyDinhDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into QuyDinh(TenQuyDinh,GiaTri,NgayThayDoi) values(?,?,?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenQuyDinh", SqlDbType.NVarChar)
        cmd.Parameters("@TenQuyDinh").Value = QDDto.TenQuyDinh
        cmd.Parameters.Add("@GiaTri", SqlDbType.VarChar)
        cmd.Parameters("@GiaTri").Value = QDDto.GiaTri
        cmd.Parameters.Add("@NgayThayDoi", SqlDbType.Date)
        cmd.Parameters("@NgayThayDoi").Value = QDDto.NgayThayDoi                
        cmd.ExecuteNonQuery()
        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        QDDto.idQuyDinh = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Sua(ByVal QDDto As QuyDinhDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update QuyDinh set TenQuyDinh=?, GiaTri=?, NgayThayDoi=? where idQuyDinh=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenQuyDinh", SqlDbType.NVarChar)
        cmd.Parameters("@TenQuyDinh").Value = QDDto.TenQuyDinh
        cmd.Parameters.Add("@GiaTri", SqlDbType.Int)
        cmd.Parameters("@GiaTri").Value = QDDto.GiaTri
        cmd.Parameters.Add("@NgayThayDoi", SqlDbType.Date)
        cmd.Parameters("@NgayThayDoi").Value = QDDto.NgayThayDoi        
        cmd.Parameters.Add("@idQuyDinh", SqlDbType.Int)
        cmd.Parameters("@idQuyDinh").Value = QDDto.idQuyDinh
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal idQD As Integer)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "delete QuyDinh where idQuyDinh=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idQuyDinh", SqlDbType.Int)
        cmd.Parameters("@idQuyDinh").Value = idQD
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function TraCuu(ByVal idQD As Integer) As QuyDinhDto
        Dim quydinh As QuyDinhDto = New QuyDinhDto()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * from QuyDinh where idQuyDinh=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idQuyDinh", SqlDbType.Int)
        cmd.Parameters("@idQuyDinh").Value = idQD
        cmd.ExecuteNonQuery()
        Dim da As SqlDataReader
        da = cmd.ExecuteReader()
        While da.Read()
            quydinh.idQuyDinh = idQD
            quydinh.TenQuyDinh = da("TenQuyDinh")
            quydinh.GiaTri = da("GiaTri")
            quydinh.NgayThayDoi = da("NgayThayDoi")            
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return quydinh
    End Function

    Public Function LayDanhSach() As List(Of QuyDinhDto)
        Dim danhSachQD As New List(Of QuyDinhDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * from QuyDinh"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim da As SqlDataReader
        da = cmd.ExecuteReader()
        While da.Read()
            Dim quydinh As QuyDinhDto = New QuyDinhDto()
            quydinh.idQuyDinh = da("idQuyDinh")
            quydinh.TenQuyDinh = da("TenQuyDinh")
            quydinh.GiaTri = da("GiaTri")                        
            quydinh.NgayThayDoi = da("NgayThayDoi")
            danhSachQD.Add(quydinh)
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return danhSachQD
    End Function

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From QuyDinh"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From QuyDinh"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        Dim cb As New SqlCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
End Class
